package com.admin.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.BillingSpBean;

@Repository
public class BillingSpDao {

	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	
	
	
	/**
	 * 绑定SP
	 * @param bsp
	 * @return
	 */
	public int insertBillingSp(BillingSpBean bsp){
		final String sql = "insert into billing_sp(billingId,spId,provinceId,count)values(?,?,?,?)";
		return jdbcTemplate.update(sql, new Object[]{bsp.getBillingId(),bsp.getSpId(),bsp.getProvinceId(),bsp.getCount()});
	}
	
	/**
	 * 解除绑定
	 * @param id
	 * @return
	 */
	public int delBillingSp(int id){
		final String sql = "delete from billing_sp where id = ?";
		return jdbcTemplate.update(sql, id);
	}
	
	/**
	 * 根据SPId解除绑定
	 * @param spId
	 * @return
	 */
	public int delBillingSpBySpId(int spId){
		final String sql = "delete from billing_sp where spId = ?";
		return jdbcTemplate.update(sql, spId);
	}
	
	/**
	 * 根据计费点ID查询绑定的SP
	 * @param billingId
	 * @return
	 * @throws SQLException
	 */
	public List<BillingSpBean> findByBillingId(int billingId,int start,int pageSize) throws SQLException{
		final String sql = "select * from billing_sp where billingId = ? order by id desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<BillingSpBean>(BillingSpBean.class), new Object[]{billingId,start,pageSize});
	}
	
	/**
	 * 根据计费点ID查询绑定关系总数
	 * @param billingId
	 * @return
	 */
	public int findCountByBillingId(int billingId){
		final String sql = "select count(0) from billing_sp where billingId = ?";
		return jdbcTemplate.queryForInt(sql,billingId);
	}
	
	/**
	 * 根据计费点ID删除绑定关系
	 * @param billingId
	 * @return
	 */
	public int delByBillingId(int billingId){
		final String sql = "delete from billing_sp where billingId = ?";
		return jdbcTemplate.update(sql, billingId);
	}
	
	/**
	 * 根据计费点ID 和SPID查询绑定关系
	 * @param spId
	 * @param billingId
	 * @return
	 * @throws SQLException
	 */
	public BillingSpBean findBillingSpBySPIdAndBillingId(int spId,int billingId) throws SQLException{
		final String sql = "select * from billing_sp where billingId = ? and spId = ?";
		return queryRunner.query(sql, new BeanHandler<BillingSpBean>(BillingSpBean.class),new Object[]{billingId,spId});
	}
	
	/**
	 * 根据计费点ID和SPID查询绑定关系
	 * @param billingId
	 * @param provinceId
	 * @return
	 * @throws SQLException
	 */
	public BillingSpBean findBillingSpByProvinceIdAndBillingId(int billingId,int provinceId) throws SQLException{
		final String sql = "select * from billing_sp where billingId = ? and provinceId = ?";
		return queryRunner.query(sql, new BeanHandler<BillingSpBean>(BillingSpBean.class),new Object[]{billingId,provinceId});
	}
	
	/**
	 * 根据SPID查询绑定关系
	 * @param spId
	 * @return
	 * @throws SQLException
	 */
	public BillingSpBean findBySpId(int spId) throws SQLException{
		final String sql = "select * from billing_sp where spId = ?";
		return queryRunner.query(sql, new BeanHandler<BillingSpBean>(BillingSpBean.class),spId);
	}
}
